package com.telecom.cms.dao;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.telecom.cms.dto.IdaTemplate;
import com.telecom.cms.dto.IdaTemplateAtomParam;
import com.telecom.cms.dto.IdaTemplateAtomResult;
import com.telecom.cms.dto.IdaTemplateAtomSql;
import com.telecom.cms.dto.SEG_BIZ_ATOM_DEFDTO;
import com.telecom.cms.dto.SEG_BIZ_GROUP_DEFDTO;
import com.telecom.cms.dto.SysLocLatn;
import com.telecom.cms.util.Constant;

public class IdaDAO 
{
	private Dao dao = new DaoImp();
	private String db = (new Constant()).getPara("DATABASE");
	private static List templateList = new ArrayList();
	
	private static List atomList = null;
	private static Map atomMap = null;
	private static List atomGroupList = null;
	private static Map groupAtoms = null;
	private static Map atomValues = null;
	private static List locLatns = null;
	
	public IdaTemplate findTemplate(int templateId){
		if (templateList.size() == 0)
			findAllTemplate();
		for (int i = 0; i < templateList.size(); i++){
			IdaTemplate template = (IdaTemplate)templateList.get(i);
			if (template.getTemplateId() == templateId){
				return template;
			}
		}
		return null;
	}
	
	public List findAllTemplate(){
		if (templateList.size() > 0)
			return templateList;
		String sql = "SELECT Template_Id,Template_Name,Template_Desc,Template_Type_Id,Anls_Type_Id, " +
			"Folder_Id,User_Id,Create_Time,Latn_Id,Sql_Stmt,Nbr_Type,Result_Template_Id " +
			"FROM " + db + ".IDA_TEMPLATE ";
		String qryAtomParam = "SELECT Template_Id,Biz_Atom_Id,Biz_Atom_Name,Show_Type_Id,Value_Type_Id," +
			"Is_Not_Empty,Is_Filt_Only,Is_Like_Match,Default_Value,Default_Value_Name,Biz_Rule,Display_Id " +
			"FROM " + db + ".IDA_TEMPLATE_ATOM_PARAM ORDER BY Template_Id,Display_Id";
		String qryAtomResult = "SELECT Template_Id,Biz_Atom_Id,Biz_Atom_Name,Is_Dimension,Calc_Rule," +
			"Result_Order_Rule,Display_Id " +
			"FROM " + db + ".IDA_TEMPLATE_ATOM_Result ORDER BY Template_Id,Display_Id";
		String qryAtomSql = "SELECT Template_Id,Param_Name,Param_Name_Cn,Is_Match_Atom,Dmn_Table_Name," +
			"Dmn_Column_Name1,Dmn_Column_Name2,Biz_Atom_Id,Biz_Atom_Name,Show_Type_Id,Value_Type_Id," +
			"Is_Not_Empty,Is_Filt_Only,Is_Like_Match,Default_Value,Default_Value_Name,Biz_Rule,Display_Id " +
			"FROM " + db + ".IDA_TEMPLATE_ATOM_SQL ORDER BY Template_Id,Display_Id";
		List pList = new ArrayList();
		List rList = new ArrayList();
		List sList = new ArrayList();
		try{
			ResultSet rs = dao.select(qryAtomParam);
			while(rs.next()){
				IdaTemplateAtomParam atom = new IdaTemplateAtomParam();
				atom.setTemplateId(rs.getInt(1));
				atom.setBizAtomId(rs.getInt(2));
				atom.setBizAtomName(rs.getString(3));
				atom.setShowTypeId(rs.getString(4));
				atom.setValueTypeId(rs.getString(5));
				atom.setIsNotEmpty(rs.getString(6));
				atom.setIsFiltOnly(rs.getString(7));
				atom.setIsLikeMatch(rs.getString(8));
				atom.setDefaultValue(rs.getString(9));
				atom.setDefaultValueName(rs.getString(10));
				atom.setBizRule(rs.getString(11));
				atom.setDisplayId(rs.getInt(12));
				pList.add(atom);
			}
			rs = dao.select(qryAtomResult);
			while(rs.next()){
				IdaTemplateAtomResult atom = new IdaTemplateAtomResult();
				atom.setTemplateId(rs.getInt(1));
				atom.setBizAtomId(rs.getInt(2));
				atom.setBizAtomName(rs.getString(3));
				atom.setIsDimension(rs.getString(4));
				atom.setCalcRule(rs.getString(5));
				atom.setResultOrderRule(rs.getString(6));
				atom.setDisplayId(rs.getInt(7));
				rList.add(atom);
			}
			rs = dao.select(qryAtomSql);
			while(rs.next()){
				IdaTemplateAtomSql atom = new IdaTemplateAtomSql();
				atom.setTemplateId(rs.getInt(1));
				atom.setParamName(rs.getString(2));
				atom.setParamNameCn(rs.getString(3));
				atom.setIsMatchAtom(rs.getString(4));
				atom.setDmnTableName(rs.getString(5));
				atom.setDmnColumnName1(rs.getString(6));
				atom.setDmnColumnName2(rs.getString(7));
				atom.setBizAtomId(rs.getInt(8));
				atom.setBizAtomName(rs.getString(9));
				atom.setShowTypeId(rs.getString(10));
				atom.setValueTypeId(rs.getString(11));
				atom.setIsNotEmpty(rs.getString(12));
				atom.setIsFiltOnly(rs.getString(13));
				atom.setIsLikeMatch(rs.getString(14));
				atom.setDefaultValue(rs.getString(15));
				atom.setDefaultValueName(rs.getString(16));
				atom.setBizRule(rs.getString(17));
				atom.setDisplayId(rs.getInt(18));
				sList.add(atom);
			}
			rs = dao.select(sql);
			while(rs.next()){
				IdaTemplate tmpt = new IdaTemplate();
				tmpt.setTemplateId(rs.getInt(1));
				tmpt.setTemplateName(rs.getString(2));
				tmpt.setTemplateDesc(rs.getString(3));
				tmpt.setTemplateTypeId(rs.getString(4));
				tmpt.setAnlsTypeId(rs.getString(5));
				tmpt.setFolderId(rs.getInt(6));
				tmpt.setUserId(rs.getString(7));
				tmpt.setCreateTime(rs.getString(8));
				tmpt.setLatnId(rs.getString(9));
				tmpt.setSqlStmt(rs.getString(10));
				tmpt.setNbrType(rs.getString(11));
				tmpt.setResultTemplateId(rs.getInt(12));
				List atomList = new ArrayList();
				if (tmpt.getTemplateTypeId().equals("10")){
					for (int i = 0; i <pList.size(); i++){
						IdaTemplateAtomParam atom = (IdaTemplateAtomParam)pList.get(i);
						if (atom.getTemplateId() == tmpt.getTemplateId())
							atomList.add(atom);
					}
				}
				else if (tmpt.getTemplateTypeId().equals("20")){
					for (int i = 0; i <rList.size(); i++){
						IdaTemplateAtomResult atom = (IdaTemplateAtomResult)rList.get(i);
						if (atom.getTemplateId() == tmpt.getTemplateId())
							atomList.add(atom);
					}
				}
				else if (tmpt.getTemplateTypeId().equals("30")){
					for (int i = 0; i <sList.size(); i++){
						IdaTemplateAtomSql atom = (IdaTemplateAtomSql)sList.get(i);
						if (atom.getTemplateId() == tmpt.getTemplateId())
							atomList.add(atom);
					}
				}
				else ;
				tmpt.setAtomList(atomList);
				templateList.add(tmpt);
			}
			rs.close();
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
		return templateList;
	}
	
	public int insertTemplate(IdaTemplate tmpt){
		int tid = 0;
		if (tmpt.getTemplateTypeId().equals("30")){
			StringBuffer sb = new StringBuffer();
			char[] c = tmpt.getSqlStmt().toCharArray();
			for(int i = 0; i < c.length; i++){
				sb.append(c[i]);
				if(c[i] == '\'')
					sb.append(c[i]);
			}
			tmpt.setSqlStmt(sb.toString());
		}
		String sql = "INSERT INTO " + db + ".IDA_TEMPLATE(Template_Name,Template_Desc,Template_Type_Id," +
				"Anls_Type_Id,Folder_Id,User_Id,Create_Time,Latn_Id,Sql_Stmt,Nbr_Type,Result_Template_Id) " +
				"VALUES('" + tmpt.getTemplateName() + "','" + tmpt.getTemplateDesc() + "','" + tmpt.getTemplateTypeId() + "'," +
				"'" + tmpt.getAnlsTypeId() + "'," + tmpt.getFolderId() +",'" + tmpt.getUserId() + "',CAST(date AS DATE FORMAT 'YYYY-MM-DDB') || time," +
				"'" + tmpt.getLatnId() + "','" + tmpt.getSqlStmt() + "','" + tmpt.getNbrType() + "'," + tmpt.getResultTemplateId() + ")";
		try{
			int result = dao.insert(sql);
			if (result > 0){
				templateList.clear();
				String qryId = "SELECT Template_Id FROM " + db + ".IDA_TEMPLATE " +
						"WHERE Template_Name = '" + tmpt.getTemplateName() + "' ORDER BY Create_Time DESC ";
				ResultSet rs = dao.select(qryId);
				if (rs.next())
					tid = rs.getInt(1);
				rs.close();
			}
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
		return tid;
	}
	
	public void updateTemplate(IdaTemplate tmpt){
		if (tmpt.getTemplateTypeId().equals("10"))
			deleteTemplateAtomParam(tmpt.getTemplateId());
		else if (tmpt.getTemplateTypeId().equals("20"))
			deleteTemplateAtomResult(tmpt.getTemplateId());
		else if (tmpt.getTemplateTypeId().equals("30"))
			deleteTemplateAtomSql(tmpt.getTemplateId());
		else ;
		if (tmpt.getTemplateTypeId().equals("30")){
			StringBuffer sb = new StringBuffer();
			char[] c = tmpt.getSqlStmt().toCharArray();
			for(int i = 0; i < c.length; i++){
				sb.append(c[i]);
				if(c[i] == '\'')
					sb.append(c[i]);
			}
			tmpt.setSqlStmt(sb.toString());
		}
		String sql = "UPDATE " + db + ".IDA_TEMPLATE SET Template_Name='" + tmpt.getTemplateName() + "'," +
				"Template_Desc='" + tmpt.getTemplateDesc() + "'," +
				"Template_Type_Id='" + tmpt.getTemplateTypeId() + "'," +
				"Anls_Type_Id='" + tmpt.getAnlsTypeId() + "'," +
				"Folder_Id=" + tmpt.getFolderId() +"," +
				"User_Id='" + tmpt.getUserId() + "'," +
				"Latn_Id='" + tmpt.getLatnId() + "'," +
				"Sql_Stmt='" + tmpt.getSqlStmt() + "'," +
				"Nbr_Type='" + tmpt.getNbrType() + "'," +
				"Result_Template_Id=" + tmpt.getResultTemplateId() + " " +
				"WHERE Template_Id=" + tmpt.getTemplateId();
		try{
			int result = dao.update(sql);
			if (result > 0)
				templateList.clear();
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
	}
	
	public void deleteTemplate(IdaTemplate tmpt){
		if (tmpt.getTemplateTypeId().equals("10"))
			deleteTemplateAtomParam(tmpt.getTemplateId());
		else if (tmpt.getTemplateTypeId().equals("20"))
			deleteTemplateAtomResult(tmpt.getTemplateId());
		else if (tmpt.getTemplateTypeId().equals("30"))
			deleteTemplateAtomSql(tmpt.getTemplateId());
		else ;
		String sql = "DELETE FROM " + db + ".IDA_TEMPLATE WHERE Template_Id=" + tmpt.getTemplateId();
		try{
			int result = dao.delete(sql);
			if (result > 0)
				templateList.clear();
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
	}
	
	public void insertTemplateAtomParam(IdaTemplateAtomParam atom){
		String sql = "INSERT INTO " + db + ".IDA_TEMPLATE_ATOM_PARAM(Template_Id,Biz_Atom_Id,Biz_Atom_Name,Show_Type_Id," +
				"Value_Type_Id,Is_Not_Empty,Is_Filt_Only,Is_Like_Match,Default_Value,Default_Value_Name,Biz_Rule,Display_Id) " +
				"VALUES(" + atom.getTemplateId() + "," + atom.getBizAtomId() + ",'" + atom.getBizAtomName() + "'," +
				"'" + atom.getShowTypeId() + "','" + atom.getValueTypeId() +"','" + atom.getIsNotEmpty() + "'," +
				"'" + atom.getIsFiltOnly() + "','" + atom.getIsLikeMatch() + "','" + atom.getDefaultValue() + "'," +
				"'" + atom.getDefaultValueName() + "','" + atom.getBizRule() + "'," + atom.getDisplayId() + ")";
		try{
			dao.insert(sql);
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
	}
	
	public void insertTemplateAtomResult(IdaTemplateAtomResult atom){
		String sql = "INSERT INTO " + db + ".IDA_TEMPLATE_ATOM_RESULT(Template_Id,Biz_Atom_Id,Biz_Atom_Name," +
				"Is_Dimension,Calc_Rule,Result_Order_Rule,Display_Id) " +
				"VALUES(" + atom.getTemplateId() + "," + atom.getBizAtomId() + ",'" + atom.getBizAtomName() + "'," +
				"'" + atom.getIsDimension() + "','" + atom.getCalcRule() +"'," +
				"'" + atom.getResultOrderRule() + "'," + atom.getDisplayId() + ")";
		try{
			dao.insert(sql);
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
	}
	
	public void insertTemplateAtomSql(IdaTemplateAtomSql atom){
		String sql = "INSERT INTO " + db + ".IDA_TEMPLATE_ATOM_SQL(Template_Id,Param_Name,Param_Name_Cn,Is_Match_Atom," +
			"Dmn_Table_Name,Dmn_Column_Name1,Dmn_Column_Name2,Biz_Atom_Id,Biz_Atom_Name,Show_Type_Id,Value_Type_Id," +
			"Is_Not_Empty,Is_Filt_Only,Is_Like_Match,Default_Value,Default_Value_Name,Biz_Rule,Display_Id) " +
			"VALUES(" + atom.getTemplateId() + ",'" + atom.getParamName() + "','" + atom.getParamNameCn() + "'," +
			"'" + atom.getIsMatchAtom() + "','" + atom.getDmnTableName() + "','" + atom.getDmnColumnName1() + "'," +
			"'" + atom.getDmnColumnName2() + "'," + atom.getBizAtomId() + ",'" + atom.getBizAtomName() + "'," +
			"'" + atom.getShowTypeId() + "','" + atom.getValueTypeId() +"','" + atom.getIsNotEmpty() + "'," +
			"'" + atom.getIsFiltOnly() + "','" + atom.getIsLikeMatch() + "','" + atom.getDefaultValue() + "'," +
			"'" + atom.getDefaultValueName() + "','" + atom.getBizRule() + "'," + atom.getDisplayId() + ")";
		try{
			dao.insert(sql);
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
	}
	
	private void deleteTemplateAtomParam(int templateId){
		String sql = "DELETE FROM " + db + ".IDA_TEMPLATE_ATOM_PARAM WHERE Template_Id=" + templateId;
		try{
			dao.delete(sql);
		}
		catch (Exception e){
			e.printStackTrace();
		}
	}
	
	private void deleteTemplateAtomResult(int templateId){
		String sql = "DELETE FROM " + db + ".IDA_TEMPLATE_ATOM_RESULT WHERE Template_Id=" + templateId;
		try{
			dao.delete(sql);
		}
		catch (Exception e){
			e.printStackTrace();
		}
	}
	
	private void deleteTemplateAtomSql(int templateId){
		String sql = "DELETE FROM " + db + ".IDA_TEMPLATE_ATOM_SQL WHERE Template_Id=" + templateId;
		try{
			dao.delete(sql);
		}
		catch (Exception e){
			e.printStackTrace();
		}
	}
	
	public List findAllAtom(){
		if (atomList != null)
			return atomList;
		atomList = new ArrayList();
		atomMap = new HashMap();
		String sql = "SELECT BIZ_ATOM_ID,BIZ_ATOM_NAME,VALUE_TYPE_ID,SHOW_TYPE_ID,CDE_SRC_TABLE_NAME," +
			"BAS_TABLE_NAME,BAS_TABLE_COL_NAME,BAS_TABLE_COL_TYPE,IS_USERD,ATOM_KIND_ID," +
			"USER_ID,CREATE_TIME,BIZ_ATOM_DESC,BIZ_ATOM_FLAG,SRC_TABLE_COL_NAME " +
			//"FROM " + db + ".SEG_BIZ_ATOM_DEF_NEW ORDER BY 1";
			"FROM PD_CMS.SEG_BIZ_ATOM_DEF_NEW ORDER BY 1";
		try{
			ResultSet rs = dao.select(sql);
			while(rs.next()){
				SEG_BIZ_ATOM_DEFDTO atom = new SEG_BIZ_ATOM_DEFDTO();
				atom.setBIZ_ATOM_ID(rs.getInt(1));
				atom.setBIZ_ATOM_NAME(rs.getString(2));
				atom.setVALUE_TYPE_ID(rs.getInt(3));
				atom.setSHOW_TYPE_ID(rs.getInt(4));
				atom.setCDE_SRC_TABLE_NAME(rs.getString(5));
				atom.setBAS_TABLE_NAME(rs.getString(6));
				atom.setBAS_TABLE_COL_NAME(rs.getString(7));
				atom.setBAS_TABLE_COL_TYPE(rs.getString(8));
				atom.setIS_USERD(rs.getString(9));
				atom.setATOM_KIND_ID(rs.getInt(10));
				atom.setUSER_ID(rs.getString(11));
				atom.setCREATE_TIME(rs.getTimestamp(12));
				atom.setBIZ_ATOM_DESC(rs.getString(13));
				atom.setBIZ_ATOM_FLAG(rs.getString(14));
				atom.setSRC_TABLE_COL_NAME(rs.getString(15));
				atomList.add(atom);
				atomMap.put(String.valueOf(atom.getBIZ_ATOM_ID()), atom);
			}
			rs.close();
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
		return atomList;
	}
	
	public SEG_BIZ_ATOM_DEFDTO findAtom(int atomId){
		if (atomMap == null)
			findAllAtom();
		return (SEG_BIZ_ATOM_DEFDTO)atomMap.get(String.valueOf(atomId));
	}
	
	public List findAllAtomGroup(){
		if (atomGroupList != null)
			return atomGroupList;
		atomGroupList = new ArrayList();
		String sql = "SELECT ATOM_GROUP_ID,ATOM_GROUP_NAME,ATOM_GROUP_DESC,IS_USERD," +
			"IMAGE_URL,BOOKMARK_ID,CREATE_TIME,USER_ID " +
			//"FROM " + db + ".SEG_BIZ_GROUP_DEF ORDER BY ATOM_GROUP_ID";
			"FROM PD_CMS.SEG_BIZ_GROUP_DEF ORDER BY ATOM_GROUP_ID";
		try{
			ResultSet rs = dao.select(sql);
			while(rs.next()){
				SEG_BIZ_GROUP_DEFDTO group = new SEG_BIZ_GROUP_DEFDTO();
				group.setATOM_GROUP_ID(rs.getInt(1));
				group.setATOM_GROUP_NAME(rs.getString(2));
				group.setATOM_GROUP_DESC(rs.getString(3));
				group.setIS_USERD(rs.getString(4));
				group.setIMAGE_URL(rs.getString(5));
				group.setBOOKMARK_ID(rs.getInt(6));
				group.setCREATE_TIME(rs.getTimestamp(7));
				group.setUSER_ID(rs.getString(8));
				atomGroupList.add(group);
			}
			rs.close();
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
		return atomGroupList;
	}
	
	public List findGroupAtoms(int groupId){
		if (atomList == null)
			findAllAtom();
		if (atomGroupList == null)
			findAllAtomGroup();
		if (groupAtoms == null){
			groupAtoms = new HashMap();
			List agRel = new ArrayList();
			//String sql = "SELECT BIZ_ATOM_ID,ATOM_GROUP_ID FROM " + db + ".SEG_ATOM_GROUP_REL ORDER BY 1";
			String sql = "SELECT BIZ_ATOM_ID,ATOM_GROUP_ID FROM PD_CMS.SEG_ATOM_GROUP_REL ORDER BY 1";
			try{
				ResultSet rs = dao.select(sql);
				while (rs.next())
					agRel.add(new int[]{rs.getInt(1),rs.getInt(2)});
				rs.close();
			}
			catch(Exception e){
				e.printStackTrace();
			}
			for (int i = 0; i < atomGroupList.size(); i++) {
				SEG_BIZ_GROUP_DEFDTO group = (SEG_BIZ_GROUP_DEFDTO)atomGroupList.get(i);
				List atoms = new ArrayList();
				for (int j = 0; j < atomList.size(); j++){
					SEG_BIZ_ATOM_DEFDTO atom = (SEG_BIZ_ATOM_DEFDTO)atomList.get(j);
					for (int k = 0; k < agRel.size(); k++){
						int[] rel = (int[])agRel.get(k);
						if (atom.getBIZ_ATOM_ID() == rel[0] && group.getATOM_GROUP_ID() == rel[1]){
							atoms.add(atom);
							break;
						}
					}
				}
				groupAtoms.put(String.valueOf(group.getATOM_GROUP_ID()), atoms);
			}
		}
		return (List)groupAtoms.get(String.valueOf(groupId));
	}
	
	public Map findAtomValueMap(){
		if (atomValues != null)
			return atomValues;
		if (atomList == null)
			findAllAtom();
		atomValues = new HashMap();
		for (int i = 0; i < atomList.size(); i++) {
			List values = new ArrayList();
			SEG_BIZ_ATOM_DEFDTO atom = (SEG_BIZ_ATOM_DEFDTO)atomList.get(i);
			String tableName = atom.getCDE_SRC_TABLE_NAME();
			if (tableName != null && !tableName.trim().equals("") && tableName.trim().toUpperCase().indexOf(".BAS_") == -1){
				String sql = "SELECT * FROM " + tableName + " ORDER BY 1";
				try {
					ResultSet rs = dao.select(sql);
					ResultSetMetaData md = rs.getMetaData();
					int cols = md.getColumnCount();
					while (rs.next()) {
						String[] value = new String[]{"","",""};
						if (cols >= 1)
							value[0] = rs.getObject(1).toString().trim();
						if (cols >= 2)
							value[1] = rs.getObject(2).toString().trim();
						if (cols >= 3)
							value[2] = rs.getObject(3).toString().trim();
						values.add(value);
					}
					rs.close();
				}
				catch (Exception e){
					e.printStackTrace();
				}
				finally{
					dao.closeconn();
				}
			}
			atomValues.put(String.valueOf(atom.getBIZ_ATOM_ID()), values);
		}
		return atomValues;
	}
	
	public List findAllLatn(){
		if (locLatns != null)
			return locLatns;
		locLatns = new ArrayList();
		String sql = "SELECT Latn_Id,Latn_Name,Area_Code,Latn_Cd,Latn_Desc FROM " + db + ".SYS_LOC_LATN ORDER BY Latn_Cd";
		try{
			ResultSet rs = dao.select(sql);
			while(rs.next()){
				SysLocLatn latn = new SysLocLatn();
				latn.setLatnid(rs.getString(1));
				latn.setLatnname(rs.getString(2));
				latn.setArea_Code(rs.getString(3));
				latn.setLatn_Cd(rs.getString(4));
				latn.setLatn_Desc(rs.getString(5));
				locLatns.add(latn);
			}
			rs.close();
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
		return locLatns;
	}
	
	public List idaQuery(String sql,int pageNo,int pageSize,boolean calcTotal){
		List data = new ArrayList();
		int idx = 0;
		int startIdx = (pageNo - 1) * pageSize + 1;
		int endIdx = pageNo * pageSize;
		try{
			ResultSet rs = dao.select(sql);
			int colCnt = rs.getMetaData().getColumnCount();
			while(rs.next()){
				idx++;
				if (idx < startIdx)
					continue;
				if (idx > endIdx)
					if (calcTotal) continue; else break;
				String[] row = new String[colCnt];
				for (int i = 0; i < row.length; i++){
					Object o = rs.getObject(i + 1);
					if (o != null)
						row[i] = o.toString();
					else
						row[i] = "";
				}
				data.add(row);
			}
			rs.close();
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
		if (calcTotal)
			data.add(0,new Integer(idx));
		return data;
	}
}